package com.eling.elcms.travel.dao.hibernate;

import com.eling.elcms.core.AppContext;
import com.eling.elcms.core.dao.hibernate.GenericDaoHibernate;
import com.eling.elcms.system.model.CommonUser;
import com.eling.elcms.travel.dao.IWelfareConsumptionCouponDao;
import com.eling.elcms.travel.model.WelfareConsumptionCoupon;
import com.eling.elcms.travel.model.WelfareConsumptionCouponDetail;
import com.eling.elcms.travel.model.view.WelfareConsumptionCouponStatisticsView;
import com.eling.elcms.travel.model.view.WelfareConsumptionCouponView;
import com.eling.elcms.travel.model.view.WelfareccView;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class WelfareConsumptionCouponDaoHibernate extends
		GenericDaoHibernate<WelfareConsumptionCoupon, Long> implements
		IWelfareConsumptionCouponDao {

	public WelfareConsumptionCouponDaoHibernate() {
		super(WelfareConsumptionCoupon.class);
	}
	
	/**
	 * 查询待发放消费券的会员
	 * @param date
	 * @return
	 * @author ly
	 */
	@SuppressWarnings("unchecked")
	public List<WelfareConsumptionCouponView> queryWelfareCard(Date date){
		WelfareConsumptionCouponView wc = new WelfareConsumptionCouponView();
		List<WelfareConsumptionCouponView> returnList = new ArrayList<WelfareConsumptionCouponView>();
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String sql = "";
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append("t.memberCertificates AS '会员证号', ");
		sb.append("bp. NAME AS '姓名',");
		sb.append("bp.sex,");
		sb.append("tpp. NAME AS '产品',");
		sb.append("tp. NAME AS '产品类型',");
		sb.append("t.startDate '开始日期',");
		sb.append("bp.mobilePhone AS '联系电话',");
		sb.append("t.pkTravelContract AS 'pkTravelContract' ");
		sb.append("FROM ");
		sb.append("travel_travelcontract t ");
		sb.append("LEFT JOIN mem_member m ON m.pkMember = t.pkMember ");
		sb.append("LEFT JOIN bd_personalinfo bp ON bp.pkPersonalInfo = m.pkPersonalInfo ");
		sb.append("LEFT JOIN travel_producttype tp ON tp.pkProductType = t.pkProductType ");
		sb.append("LEFT JOIN travel_product tpp ON tp.pkProduct = tpp.pkProduct ");
		sb.append("LEFT JOIN travel_welfare tw ON tw.pkTravelContract = t.pkTravelContract ");
		sb.append("WHERE DATE_SUB(CURDATE(),INTERVAL 1 YEAR)>t.startDate  AND (tw.isGrant=0 OR tw.isGrant IS NULL) AND t.`status` = 'Valid' ");
		sb.append("ORDER BY t.startDate DESC");
		sql = sb.toString();
		List<Object[]> list = getSession().createNativeQuery(sql).list();
		for(int i = 0;i<list.size();i++){
			Object[] o = list.get(i);
			wc = new WelfareConsumptionCouponView();
			if(o[0]!=null){
				wc.setCerCard(o[0].toString());
			}
			if(o[1]!=null){
				wc.setName(o[1].toString());
			}
			if(o[2]!=null){
				wc.setSex(o[2].toString());
			}
			if(o[3]!=null){
				wc.setProductName(o[3].toString());
			}
			if(o[4]!=null){
				wc.setProductType(o[4].toString());
			}
			if(o[5]!=null){
				try {
					wc.setStartDate(df.parse(o[5].toString()));
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
			if(o[6]!=null){
				wc.setMobilePhone(o[6].toString());
			}
			if(o[7]!=null){
				wc.setPktravelContract(o[7].toString());
			}
			returnList.add(wc);
		}
		return returnList;
	}
	
	/**
	 *  验证卡号是否已经被使用
	 * @return
	 * @author ly
	 */
	public Integer queryWelfareCardAjax(String cardNo){
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append("count(tw.cardNo) AS count ");
		sb.append("FROM ");
		sb.append("travel_welfare tw ");
		sb.append("WHERE ");
		sb.append("tw.cardNo =");
		sb.append(cardNo);
		Object o = getSession().createNativeQuery(sb.toString()).uniqueResult();
		String s = o.toString();
		return Integer.parseInt(s);
	}
	
	/**
	 * 根据条件的搜索
	 * @param value
	 * @return
	 * @author ly
	 */
	@SuppressWarnings("unchecked")
	public List<WelfareConsumptionCouponView> queryWelfareCardSerarch(String value){
		WelfareConsumptionCouponView wc = new WelfareConsumptionCouponView();
		List<WelfareConsumptionCouponView> returnList = new ArrayList<WelfareConsumptionCouponView>();
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String sql = "";
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append("t.memberCertificates AS '会员证号', ");
		sb.append("bp.NAME AS '姓名',");
		sb.append("bp.sex,");
		sb.append("tpp.NAME AS '产品',");
		sb.append("tp.NAME AS '产品类型',");
		sb.append("t.startDate '开始日期',");
		sb.append("bp.mobilePhone AS '联系电话',");
		sb.append("t.pkTravelContract AS 'pkTravelContract' ");
		sb.append("FROM ");
		sb.append("travel_travelcontract t ");
		sb.append("LEFT JOIN mem_member m ON m.pkMember = t.pkMember ");
		sb.append("LEFT JOIN bd_personalinfo bp ON bp.pkPersonalInfo = m.pkPersonalInfo ");
		sb.append("LEFT JOIN travel_producttype tp ON tp.pkProductType = t.pkProductType ");
		sb.append("LEFT JOIN travel_product tpp ON tp.pkProduct = tpp.pkProduct ");
		sb.append("LEFT JOIN travel_welfare tw ON tw.pkTravelContract = t.pkTravelContract ");
		sb.append("WHERE DATE_SUB(CURDATE(),INTERVAL 1 YEAR)>t.startDate AND t.`status` = 'Valid' AND (tw.isGrant=0 OR tw.isGrant IS NULL) ");
		
		if(value!=null && value.length()>0){
			String s1 = value;
			sb.append("AND (bp.NAME LIKE '%");
			sb.append(s1);
			sb.append("%' OR t.memberCertificates LIKE '%");
			sb.append(s1);
			sb.append("%') ");
		}
		sb.append("ORDER BY t.startDate DESC");
		sql = sb.toString();
		List<Object[]> list = getSession().createNativeQuery(sql).list();
		for(int i = 0;i<list.size();i++){
			Object[] o = list.get(i);
			wc = new WelfareConsumptionCouponView();
			if(o[0]!=null){
				wc.setCerCard(o[0].toString());
			}
			if(o[1]!=null){
				wc.setName(o[1].toString());
			}
			if(o[2]!=null){
				wc.setSex(o[2].toString());
			}
			if(o[3]!=null){
				wc.setProductName(o[3].toString());
			}
			if(o[4]!=null){
				wc.setProductType(o[4].toString());
			}
			if(o[5]!=null){
				try {
					wc.setStartDate(df.parse(o[5].toString()));
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
			if(o[6]!=null){
				wc.setMobilePhone(o[6].toString());
			}
			if(o[7]!=null){
				wc.setPktravelContract(o[7].toString());
			}
			returnList.add(wc);
		}
		return returnList;
	}
	
	/**
	 * 根据条件的搜索
	 * @param value,cond,endDate
	 * @return
	 * @author ly
	 */
	@SuppressWarnings("rawtypes")
	public List<Long> queryWelfareCardSerarchGrid(String value,WelfareConsumptionCoupon cond,String endDate){
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String pkServicePoint = cond.getServicePoint().getPkServicePoint().toString();
		Date grantDate = cond.getGrantDate();
		String startDate = df.format(grantDate);
		Date date = new Date(Long.parseLong(endDate));
		endDate = df.format(date);
		String status = cond.getStatus().toString();
		
		List<Long> returnList = new ArrayList<Long>();
		String sql = "";
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append("tw.pkWelfarecc ");
		sb.append("FROM ");
		sb.append("travel_welfare tw ");
		sb.append("LEFT JOIN travel_travelcontract tt ON tw.pkTravelContract = tt.pkTravelContract ");
		sb.append("LEFT JOIN mem_member m ON m.pkMember = tt.pkMember ");
		sb.append("LEFT JOIN bd_personalinfo bp ON bp.pkPersonalInfo = m.pkPersonalInfo ");
		sb.append("WHERE ");
		if(value!=null && value.length()>0){
				String s1 = value;
				sb.append("(tw.cardNo LIKE '%");
				sb.append(s1);
				sb.append("%' OR tt.memberCertificates LIKE '%");
				sb.append(s1);
				sb.append("%' OR bp.name LIKE '%");
				sb.append(s1);
				sb.append("%') AND ");
		}
		
		sb.append("tw.pkServicePoint=");
		sb.append(pkServicePoint);
		sb.append(" AND tw.status ='");
		sb.append(status);
		sb.append("' AND tw.grantDate BETWEEN '");
		sb.append(startDate);
		sb.append("' AND '");
		sb.append(endDate);
		sb.append("' ");
		sb.append("ORDER BY tw.grantDate DESC");
		sql = sb.toString();
		List list = getSession().createNativeQuery(sql).list();
		for(int i = 0;i<list.size();i++){
			returnList.add(Long.parseLong(list.get(i).toString()));
		}
		return returnList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Long> queryRule(WelfareConsumptionCoupon cond) {
		StringBuffer sql = new StringBuffer();
		sql.append("	SELECT ");
		sql.append("		w.pkWelfarecc ");
		sql.append("	FROM ");
		sql.append("		travel_productrule p ");
		sql.append("	LEFT JOIN travel_travelcontract t ON t.pkProductRule = p.pkProductRule ");
		sql.append("	LEFT JOIN travel_welfare w ON w.pkTravelContract = t.pkTravelContract ");
		sql.append("	LEFT JOIN om_servicepoint sp ON w.pkServicePoint = sp.pkServicePoint ");
		sql.append("	LEFT JOIN travel_productruledetail pd ON p.pkProductRule = pd.pkProductRule ");
		sql.append("	LEFT JOIN travel_pd_settlementsrule se ON pd.pkProductRuleDetail = se.pkProductRuleDetail ");
		sql.append("	LEFT JOIN travel_settlementsrule sr ON se.pkSettlementsRule = sr.pkSettlementsRule ");
		sql.append("	WHERE ");
		sql.append("		sr.type = '"+com.eling.elcms.travel.model.RuleType.Coupon+"' ");
		sql.append("	AND w.pkWelfarecc IS NOT NULL ");
		sql.append("	AND w.`status`='"+WelfareConsumptionCoupon.Status.Effective+"' ");
		sql.append("	AND sp.pkServicePoint = "+((CommonUser)AppContext.curUser()).getServicePoint().getPkServicePoint()+" ");
		List<Long> list = getSession().createNativeQuery(sql.toString()).list();
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<WelfareccView> queryDetail(List<Long> pklist) {
		StringBuffer sql = new StringBuffer();
		sql.append("	SELECT ");
		sql.append("		t.pkWelfarecc,w.cardNo,IFNULL(t.number,0),IFNULL(t.total,0),IFNULL(t1.total,0),IFNULL(t2.total,0),IFNULL(w.balance,0) ");
		sql.append("	FROM ");
		sql.append("		travel_welfare w ");
		sql.append("	LEFT JOIN (		 ");
		sql.append("		SELECT ");
		sql.append("			w.pkWelfarecc, ");
		sql.append("			COUNT(wd.pkWelfareccd) AS number, ");
		sql.append("			SUM(wd.price) AS total ");
		sql.append("		FROM ");
		sql.append("			travel_welfare w ");
		sql.append("		LEFT JOIN travel_welfaredetail wd ON w.pkWelfarecc = wd.pkWelfarecc ");
		sql.append("		WHERE ");
		sql.append("			wd.operateStatus = '"+WelfareConsumptionCouponDetail.OperateStatus.ReturnCertificate+"' ");
		sql.append("		GROUP BY  w.pkWelfarecc ) t ON w.pkWelfarecc = t.pkWelfarecc ");
		sql.append("	LEFT JOIN (		 ");
		sql.append("		SELECT ");
		sql.append("			w.pkWelfarecc, ");
		sql.append("			SUM(wd.price) AS total ");
		sql.append("		FROM ");
		sql.append("			travel_welfare w ");
		sql.append("		LEFT JOIN travel_welfaredetail wd ON w.pkWelfarecc = wd.pkWelfarecc ");
		sql.append("		WHERE ");
		sql.append("			wd.operateStatus = '"+WelfareConsumptionCouponDetail.OperateStatus.Cash+"' ");
		sql.append("		GROUP BY  w.pkWelfarecc ) t1 ON w.pkWelfarecc = t1.pkWelfarecc ");
		sql.append("	LEFT JOIN (		 ");
		sql.append("		SELECT ");
		sql.append("			w.pkWelfarecc, ");
		sql.append("			SUM(wd.price) AS total ");
		sql.append("		FROM ");
		sql.append("			travel_welfare w ");
		sql.append("		LEFT JOIN travel_welfaredetail wd ON w.pkWelfarecc = wd.pkWelfarecc ");
		sql.append("		WHERE ");
		sql.append("			wd.operateStatus = '"+WelfareConsumptionCouponDetail.OperateStatus.Consumption+"' ");
		sql.append("		GROUP BY  w.pkWelfarecc ) t2 ON w.pkWelfarecc = t2.pkWelfarecc ");
		sql.append("	WHERE ");
		sql.append("		w.pkWelfarecc IN ("+StringUtils.join(pklist, ",")+") ");
		List<Object[]> list = getSession().createNativeQuery(sql.toString()).list();
		List<WelfareccView> returnList = new ArrayList<WelfareccView>();
		for(Object[] o : list){
			WelfareccView view = new WelfareccView();
			view.setPkWelfarecc(o[0] == null ? null : Long.valueOf(o[0].toString()));
			view.setCardNo(o[1] == null ? null : o[1].toString());
			view.setCount(o[2] == null ? null : Integer.valueOf(o[2].toString()));
			view.setReturnCertificateTotal(o[3] == null ? null : Double.valueOf(o[3].toString()));
			view.setCashTotal(o[4] == null ? null : Double.valueOf(o[4].toString()));
			view.setConsumptionTotal(o[5] == null ? null : Double.valueOf(o[5].toString()));
			view.setBalance(o[6] == null ? null : Double.valueOf(o[6].toString()));
			returnList.add(view);
		}
		return returnList;
	}
	@SuppressWarnings("unchecked")
	@Override
	public List<WelfareConsumptionCouponStatisticsView> queryStatistics(Long pkTravelContract) {
		
        StringBuffer sql = new StringBuffer();
        sql.append("    SELECT");
        sql.append("        w.pkWelfarecc,w.cardNo,t1.returnNumber,t1.returnTotal,t2.consumptionTotal,t3.cashTotal,w.balance");
        sql.append("    FROM travel_welfare w");
        sql.append("    LEFT JOIN travel_travelcontract tc on w.pkTravelContract=tc.pkTravelContract");
        sql.append("    LEFT JOIN (");
        sql.append("        SELECT");
        sql.append("            w.pkWelfarecc,COUNT(wd.pkWelfareccd) AS returnNumber,SUM(wd.price) AS returnTotal");
        sql.append("        FROM travel_welfare w");
        sql.append("        LEFT JOIN travel_welfaredetail wd ON w.pkWelfarecc = wd.pkWelfarecc");
        sql.append("        WHERE wd.operateStatus = 'ReturnCertificate'");
        sql.append("        GROUP BY w.pkWelfarecc");
        sql.append("    ) t1 ON w.pkWelfarecc = t1.pkWelfarecc");
        sql.append("    LEFT JOIN (");
        sql.append("        SELECT ");
        sql.append("            w.pkWelfarecc,IFNULL(SUM(wd.price), 0) AS consumptionTotal");
        sql.append("        FROM travel_welfare w");
        sql.append("        LEFT JOIN travel_welfaredetail wd ON w.pkWelfarecc = wd.pkWelfarecc");
        sql.append("        WHERE    wd.operateStatus = 'Consumption'");
        sql.append("        GROUP BY w.pkWelfarecc");
        sql.append("    ) t2 ON w.pkWelfarecc = t2.pkWelfarecc");
        sql.append("    LEFT JOIN (");
        sql.append("        SELECT w.pkWelfarecc,IFNULL(SUM(wd.price), 0) AS cashTotal");
        sql.append("        FROM travel_welfare w");
        sql.append("        LEFT JOIN travel_welfaredetail wd ON w.pkWelfarecc = wd.pkWelfarecc");
        sql.append("        WHERE wd.operateStatus = 'Cash'");
        sql.append("        GROUP BY w.pkWelfarecc");
        sql.append("    ) t3 ON w.pkWelfarecc = t3.pkWelfarecc");
        sql.append("    WHERE 1=1");
        if(pkTravelContract!=null){
            sql.append("    and tc.pkTravelContract = " + pkTravelContract);    
        }
        
        List<Object[]> list = getSession().createNativeQuery(sql.toString()).list();
		
        List<WelfareConsumptionCouponStatisticsView> wccsList = new ArrayList<WelfareConsumptionCouponStatisticsView>();
        
        for(int i=0;i<list.size();i++){
        	WelfareConsumptionCouponStatisticsView w = new WelfareConsumptionCouponStatisticsView();
        	Object[] o = list.get(i);
			if(o[0]!=null){
				w.setPkWelfarecc(new Long(o[0].toString()));
			}
			if(o[1]!=null){
				w.setCardNo(o[1].toString());
			}
			if(o[2]!=null){
				w.setReturnNumber(new Integer(o[2].toString()));
			}
			if(o[3]!=null){
				w.setReturnTotal(new Double(o[3].toString()));
			}
			if(o[4]!=null){
				w.setConsumptionTotal(new Double(o[4].toString()));
			}
			if(o[5]!=null){
				w.setCashTotal(new Double(o[5].toString()));
			}
			if(o[6]!=null){
				w.setBalance(new Double(o[6].toString()));
			}
			wccsList.add(w);
        }
        return wccsList;
	}

}
